﻿using Dapper;
using System.Data.SqlClient;
using yunbo.Model.BIW;

namespace yunbo.Repositorys.BIWRepositorys
{
    public class Wbd_kwRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";

        public FYWbd_kw GetfYWbd_Kw(int PageNumber, int PageSize)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @$" select w.*,c.ck_name,q.kq_name
                                from [dbo].[wbd_kw] w
                                inner join [dbo].[wbd_ck] c on w.kw_ssck=c.ck_code
                                inner join [dbo].[wbd_kq] q on w.kw_sskq=q.kq_code
                                WHERE w.[is_deleted] = 1
                                ORDER BY [kw_id]
                                OFFSET ({PageNumber}-1)*{PageSize} ROWS
                                FETCH NEXT {PageSize} ROWS ONLY;";
                List<Wbd_kw> wbd_Kws = connection.Query<Wbd_kw>(sql).ToList();
                string sql1 = $"SELECT COUNT(*) FROM [dbo].[wbd_kw]";
                int zon = connection.Query<int>(sql1).FirstOrDefault();
                FYWbd_kw fYWbd_Kw = new FYWbd_kw() 
                {
                    wbd_Kws=wbd_Kws,
                    zon=zon,
                };
                return fYWbd_Kw;
            }
        }
        public int AddWbd_kw(Wbd_kw wbd_kw)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $@" insert into [dbo].[wbd_kw]([kw_code],[kw_name],[kw_zt],[kw_mj],[kw_zzl],[kw_x],[kw_y],[kw_z],[kw_ssck],[kw_sskq],[bz],[is_deleted],[create_by],[create_time])
                                 values('{wbd_kw.kw_code}','{wbd_kw.kw_name}','{wbd_kw.kw_zt}','{wbd_kw.kw_mj}','{wbd_kw.kw_zzl}','{wbd_kw.kw_x}','{wbd_kw.kw_y}','{wbd_kw.kw_z}','{wbd_kw.kw_ssck}','{wbd_kw.kw_sskq}','{wbd_kw.bz}','{wbd_kw.is_deleted}','{wbd_kw.create_by}','{wbd_kw.create_time}')";
                int kw = connection.Execute(sql);
                return kw;
            }
        }
        public int UpWbd_kw(Wbd_kw wbd_Kw )
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $@"update [dbo].[wbd_kw] set kw_code='{wbd_Kw.kw_code}', kw_name='{wbd_Kw.kw_name}', kw_mj='{wbd_Kw.kw_mj}', kw_zzl='{wbd_Kw.kw_zzl}', kw_x='{wbd_Kw.kw_x}', kw_y='{wbd_Kw.kw_y}', kw_z='{wbd_Kw.kw_z}', 
                                kw_ssck='{wbd_Kw.kw_ssck}', kw_sskq='{wbd_Kw.kw_sskq}', bz='{wbd_Kw.bz}', update_by='{wbd_Kw.update_by}', update_time='{wbd_Kw.update_time}'
                               where [kw_id]='{wbd_Kw.kw_id}'";
                int kw = connection.Execute(sql);
                return kw;
            }
        }
        public int IsDeWbd_kw(int id)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wbd_kw] set [is_deleted]=0 where [kw_id]='{id}' ";
                int kws = connection.Execute(sql);
                return kws;
            }
        }
        public int UpZt(int id,int zt)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"update [dbo].[wbd_kw] set kw_zt='{zt}' where kw_id='{id}'";
                int kws = connection.Execute(sql);
                return kws;
            }
        }
        public List<Wbd_kw> Getkw(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select kw_name,kw_code from [dbo].[wbd_kw] where kw_sskq='{code}'";
                List<Wbd_kw> kw= connection.Query<Wbd_kw>(sql).ToList();
                return kw;
            }
        }

    }
}
